package com.roc.web.action.estrn;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;

public class NoEstrnMerchantInfoSearchCondition extends BaseSearchCondition {
	
	private String mchtId;//商户号
	
	private String mchtName;//商户名称
	
	private String termCode;//终端号
	
	private String trnRange ;// 交易查询时间   0,15天  ；1,一个月；
	
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {

		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
		
		if(Constance.isNotEmpty(mchtId)){
			filedBeanList.add(new WhereHQLFieldBean("M1.merc_code", mchtId, "=", false));
		}
		
		if(Constance.isNotEmpty(mchtName)){
         	filedBeanList.add(new WhereHQLFieldBean("M1.merc_name",mchtName.trim(),"=", false));
       	}
		
		
		if(Constance.isNotEmpty(termCode)){
         	filedBeanList.add(new WhereHQLFieldBean("M1.term_code",termCode.trim(),"=", false));
       	}
		
		return filedBeanList;
	}

	@Override
	protected String getRowCountHQL() {
		StringBuffer sb = new StringBuffer();
		
		int r_date = 15;
		if(Constance.isNotEmpty(trnRange)){
			r_date =   trnRange.equals("1") ? 30 : 15 ;
       	}
		sb.append(" with OPE as ");
		sb.append("  (select his_trn_log.mchnt_cd merc_code,his_trn_log.term_id term_code ");
		sb.append("     from apstfr.tbl_tfr_his_trn_log his_trn_log ");
		sb.append("    where his_trn_log.RESP_CD = '00' ");
		sb.append("      AND his_trn_log.trans_st = '1000000000' ");
		sb.append("      AND trim(his_trn_log.MA_TRANS_CD) IN ('1011', '1171') ");
		sb.append("      AND his_trn_log.TRANS_DT >= to_char(sysdate - " + r_date + ", 'yyyyMMdd') ");
		sb.append("      AND his_trn_log.TRANS_DT <= to_char(sysdate - 1, 'yyyyMMdd') ");
		sb.append("    group by his_trn_log.mchnt_cd,his_trn_log.term_id) ");
		sb.append(" SELECT count(1) ");
		sb.append("   FROM (SELECT t1.real_name supri_name, ");
		sb.append("                (select om.org_name ");
		sb.append("                   from tbl_org_main om ");
		sb.append("                  where om.org_code=m.uc_bc_cd_area) oneagentname, ");
		sb.append("                (select A1.Name ");
		sb.append("                   from tbl_area A1 ");
		sb.append("                  where A1.id= T1.companyid) companyname, ");
		sb.append("                t.MCHT_CD merc_code, ");
		sb.append("                m.name_busi merc_name, ");
		sb.append("                t.TERM_ID term_code, ");
		sb.append("                (case ");
		sb.append("                  when t.status = '00' then ");
		sb.append("                   '否' ");
		sb.append("                  when t.status = '01' then ");
		sb.append("                   '是' ");
		sb.append("                end) status, ");
		sb.append("                t.DEVICE_CD serial_num, ");
		sb.append("                to_char(m.appr_date, 'yyyy-MM-dd HH24:mi:ss') appr_date, ");
		sb.append("                (select o1.real_name from apsmgm.tbl_mcht_extend_info t , apsmgm.t_operators o1 where t.develop_person = to_char(o1.id) and  t.mcth_cd = m.mcht_cd) develop_person , ");
		// 终端型号，绑定时间，装机地址
		sb.append("                t.TMNL_BRAND, ");
		sb.append("                (select p.pos_name from yj_pos_info p where p.serial_num = t.device_cd and p.pos_brand = t.tmnl_brand) tmnl_model_no ,  ");
		sb.append("                to_char(t.rec_upd_ts, 'yyyy-MM-dd HH24:mi:ss') , ");
		sb.append("                t.installlocation ");
		
		sb.append("           from tbl_term_inf t, ");
		sb.append("                tbl_mcht_inf m, ");
		sb.append("                t_operators  t1 ");
		sb.append("          where t.MCHT_CD = m.mcht_cd ");
		sb.append("            and m.ext1 = t1.id ");
		sb.append("            and instr(t.DEVICE_CD, 'YJ') <= 0 ");
		sb.append("            and t.REC_UPD_TS is not null ");
		sb.append("            and m.group_cd = 2) M1 ");
	//	sb.append("            ) M1 ");
		sb.append("  WHERE NOT EXISTS (SELECT 1 ");
		sb.append("           FROM OPE OPE ");
		sb.append("          WHERE OPE.merc_code = M1.merc_code ");
		sb.append("            AND OPE.term_code = M1.term_code) ");

		
		return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
		StringBuffer sb = new StringBuffer();
		
		int r_date = 15;
		if(Constance.isNotEmpty(trnRange)){
			r_date =   trnRange.equals("1") ? 30 : 15 ;
       	}
		sb.append(" with OPE as ");
		sb.append("  (select his_trn_log.mchnt_cd merc_code,his_trn_log.term_id term_code ");
		sb.append("     from apstfr.tbl_tfr_his_trn_log his_trn_log ");
		sb.append("    where his_trn_log.RESP_CD = '00' ");
		sb.append("      AND his_trn_log.trans_st = '1000000000' ");
		sb.append("      AND trim(his_trn_log.MA_TRANS_CD) IN ('1011', '1171') ");
		sb.append("      AND his_trn_log.TRANS_DT >= to_char(sysdate - " + r_date + ", 'yyyyMMdd') ");
		sb.append("      AND his_trn_log.TRANS_DT <= to_char(sysdate - 1, 'yyyyMMdd') ");
		sb.append("    group by his_trn_log.mchnt_cd,his_trn_log.term_id) ");
		sb.append(" SELECT M1.* ");
		sb.append("   FROM (SELECT t1.real_name supri_name, ");
		sb.append("                (select om.org_name ");
		sb.append("                   from tbl_org_main om ");
		sb.append("                  where om.org_code=m.uc_bc_cd_area) oneagentname, ");
		sb.append("                (select A1.Name ");
		sb.append("                   from tbl_area A1 ");
		sb.append("                  where A1.id= T1.companyid) companyname, ");
		sb.append("                t.MCHT_CD merc_code, ");
		sb.append("                m.name_busi merc_name, ");
		sb.append("                t.TERM_ID term_code, ");
		sb.append("                (case ");
		sb.append("                  when t.status = '00' then ");
		sb.append("                   '否' ");
		sb.append("                  when t.status = '01' then ");
		sb.append("                   '是' ");
		sb.append("                end) status, ");
		sb.append("                t.DEVICE_CD serial_num, ");
		sb.append("                to_char(m.appr_date, 'yyyy-MM-dd HH24:mi:ss') appr_date, ");
		sb.append("                (select o1.real_name from apsmgm.tbl_mcht_extend_info t , apsmgm.t_operators o1 where t.develop_person = to_char(o1.id) and  t.mcth_cd = m.mcht_cd) develop_person , ");
		// 终端型号，绑定时间，装机地址
		sb.append("                t.TMNL_BRAND, ");
		sb.append("                (select p.pos_name from yj_pos_info p where p.serial_num = t.device_cd and p.pos_brand = t.tmnl_brand) tmnl_model_no ,  ");
		sb.append("                to_char(t.rec_upd_ts, 'yyyy-MM-dd HH24:mi:ss') , ");
		sb.append("                t.installlocation ");
		
		sb.append("           from tbl_term_inf t, ");
		sb.append("                tbl_mcht_inf m, ");
		sb.append("                t_operators  t1 ");
		sb.append("          where t.MCHT_CD = m.mcht_cd ");
		sb.append("            and m.ext1 = t1.id ");
		sb.append("            and instr(t.DEVICE_CD, 'YJ') <= 0 ");
		sb.append("            and t.REC_UPD_TS is not null ");
		sb.append("            and m.group_cd = 2) M1 ");
		sb.append("  WHERE NOT EXISTS (SELECT 1 ");
		sb.append("           FROM OPE OPE ");
		sb.append("          WHERE OPE.merc_code = M1.merc_code ");
		sb.append("            AND OPE.term_code = M1.term_code) ");

		return sb.toString();
	}

	@Override
	protected String getOrderPartOfHQL() {
		// TODO Auto-generated method stub
		return " ORDER BY M1.merc_code,M1.term_code ";
	}

	public String getMchtId() {
		return mchtId;
	}

	public void setMchtId(String mchtId) {
		this.mchtId = mchtId;
	}

	public String getMchtName() {
		return mchtName;
	}

	public void setMchtName(String mchtName) {
		this.mchtName = mchtName;
	}

	public String getTermCode() {
		return termCode;
	}

	public void setTermCode(String termCode) {
		this.termCode = termCode;
	}

	public String getTrnRange() {
		return trnRange;
	}

	public void setTrnRange(String trnRange) {
		this.trnRange = trnRange;
	}

}
